/** <a href="http://www.cpupk.com/decompiler">Eclipse Class Decompiler</a> plugin, Copyright (c) 2017 Chen Chao. **/
package com.minierp.dao.hibernate;

import com.minierp.dao.EstablishmentCommunicateDao;
import com.minierp.dao.hibernate.BaseDaoImpl;
import com.minierp.model.EstCommReceiver;
import com.minierp.model.EstablishmentCommunicate;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

public class EstablishmentCommunicateDaoImpl extends BaseDaoImpl
		implements
			EstablishmentCommunicateDao {
	public int delete(final long id) {
		Object obj = this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						String hql = "delete com.minierp.model.EstablishmentCommunicate where id = :id";
						return new Integer(session.createQuery(hql)
								.setLong("id", id).executeUpdate());
					}
				});
		return ((Integer) obj).intValue();
	}

	public int estCorporate(final long fromClientId, final long toClientId) {
		Object obj = this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						String hql = "update com.minierp.model.EstablishmentCommunicate set estId="
								+ toClientId + " where estId=" + fromClientId;
						Query q = session.createQuery(hql);
						return new Integer(q.executeUpdate());
					}
				});
		return ((Integer) obj).intValue();
	}

	public List<?> getReceiverList(long commId) {
		String sql = "select c,s from com.minierp.model.EstCommReceiver c ,com.minierp.model.Staffer s where c.receiverId=s.id and c.commId="
				+ commId + " order by s.nameSpell";
		return this.getHibernateTemplate().find(sql);
	}

	public List<?> getReceiverList(String tmpCode) {
		String sql = "select c,s from com.minierp.model.EstCommReceiver c ,com.minierp.model.Staffer s where c.receiverId=s.id and c.tmpCode=\'"
				+ tmpCode + "\'" + " order by s.nameSpell";
		return this.getHibernateTemplate().find(sql);
	}

	public List<?> getReceiverList(String temCode, long commId) {
		String sql = "from com.minierp.model.EstCommReceiver c where c.commId="
				+ commId + " and c.tmpCode=\'" + temCode + "\'";
		List list = this.getHibernateTemplate().find(sql);
		return list;
	}

	public EstCommReceiver getReceiver(String tmpCode, long receiverId) {
		String sql = "from com.minierp.model.EstCommReceiver c where c.receiverId="
				+ receiverId + " and c.tmpCode=\'" + tmpCode + "\'";
		List list = this.getHibernateTemplate().find(sql);
		return list.size() > 0 ? (EstCommReceiver) list.get(0) : null;
	}

	public EstCommReceiver getReceiver(long commId, long receiverId) {
		String sql = "from com.minierp.model.EstCommReceiver c where c.receiverId="
				+ receiverId + " and c.commId=" + commId;
		List list = this.getHibernateTemplate().find(sql);
		return list.size() > 0 ? (EstCommReceiver) list.get(0) : null;
	}

	public int deleteReceiver(final long id) {
		Object obj = this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						String hql = "delete com.minierp.model.EstCommReceiver where id = :id";
						return new Integer(session.createQuery(hql)
								.setLong("id", id).executeUpdate());
					}
				});
		return ((Integer) obj).intValue();
	}

	public int updateReceiver(final String tmpCode, final long commId) {
		Object obj = this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						String hql = "update com.minierp.model.EstCommReceiver set commId=:commId where tmpCode=:tmpCode";
						return new Integer(session.createQuery(hql)
								.setLong("commId", commId)
								.setString("tmpCode", tmpCode).executeUpdate());
					}
				});
		return ((Integer) obj).intValue();
	}

	public List<?> getList(Date startDate, Date endDate, long stafferId) {
		String sql = "select c.* from (select a.* from  establishment_communicate a where a.comm_type=\'LOG\' and a.creater_id="
				+ stafferId
				+ " and a.communicate_time>=:startDate"
				+ " and a.communicate_time<:endDate"
				+ " union all"
				+ " select a.* from "
				+ " establishment_communicate a"
				+ " where a.comm_type=\'"
				+ "PLAN"
				+ "\'"
				+ " and (a.creater_id="
				+ stafferId
				+ " or a.for_all=\'Y\')"
				+ " and a.communicate_time>=:startDate"
				+ " and a.communicate_time<:endDate"
				+ " union all"
				+ " select a.* from"
				+ " establishment_communicate a"
				+ " ,est_comm_receiver b"
				+ " where a.id=b.comm_id"
				+ " and a.comm_type=\'"
				+ "PLAN"
				+ "\'"
				+ " and a.creater_id!="
				+ stafferId
				+ " and a.for_all=\'N\'"
				+ " and a.communicate_time>=:startDate"
				+ " and a.communicate_time<:endDate"
				+ " and b.receiver_id="
				+ stafferId + ") c" + " order by c.communicate_time";
		Session sess = null;

		List arg8;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			q.setDate("startDate", startDate);
			q.setDate("endDate", endDate);
			q.addEntity(EstablishmentCommunicate.class);
			arg8 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg8;
	}

	public int complete(final long id, final String mark) {
		Object obj = this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						String hql = "update com.minierp.model.EstablishmentCommunicate set completed=\'Y\',sendSms=\'N\',completeDate=:completeDate,completeMark=:mark where id="
								+ id;
						return new Integer(session.createQuery(hql)
								.setTimestamp("completeDate", new Date())
								.setString("mark", mark).executeUpdate());
					}
				});
		return ((Integer) obj).intValue();
	}

	public List<?> getSmsList() {
		String sql = "from com.minierp.model.EstablishmentCommunicate c where c.sendSms=\'Y\'";
		return this.getHibernateTemplate().find(sql);
	}

	public List<?> getWillDoList(long stafferId) {
		String sql = "select c.* from ( select a.* from  establishment_communicate a where a.comm_type=\'PLAN\' and (a.creater_id="
				+ stafferId
				+ " or a.for_all=\'Y\')"
				+ " and a.completed=\'N\'"
				+ " union all"
				+ " select a.* from"
				+ " establishment_communicate a"
				+ " ,est_comm_receiver b"
				+ " where a.id=b.comm_id"
				+ " and a.comm_type=\'"
				+ "PLAN"
				+ "\'"
				+ " and a.creater_id!="
				+ stafferId
				+ " and a.for_all=\'N\'"
				+ " and a.completed=\'N\'"
				+ " and b.receiver_id="
				+ stafferId
				+ ") c"
				+ " order by c.communicate_time desc";
		Session sess = null;

		List arg6;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			q.addEntity(EstablishmentCommunicate.class);
			arg6 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg6;
	}

	public List<?> getList(Date startDate, Date endDate, String type,
			String completed, long stafferId, long createrId, String fromType,
			int startIndex, int num) {
		String subSql = this.getSubSql(startDate, endDate, type, completed,
				stafferId, createrId, fromType);
		String sql = "select c.id,c.comm_type,c.communicate_time,c.title,c.content,c.creater_id,c.creater_name,c.completed,c.sms_remind,c.complete_mark,c.attach_file,e.est_name from ("
				+ subSql
				+ ") c left join establishment e on c.est_id=e.id"
				+ " order by c.communicate_time desc";
		Session sess = null;

		List arg16;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			if (type != null && !type.equals("A")) {
				q.setString("commType", type);
			}

			if (completed != null && !completed.equals("A")
					&& (type == null || !type.equals("LOG"))) {
				q.setString("completed", completed);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg16 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg16;
	}

	public int getListCount(Date startDate, Date endDate, String type,
			String completed, long stafferId, long createrId, String fromType) {
		String subSql = this.getSubSql(startDate, endDate, type, completed,
				stafferId, createrId, fromType);
		String sql = "select count(c.id) from (" + subSql + ") c";
		Session sess = null;

		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			if (type != null && !type.equals("A")) {
				q.setString("commType", type);
			}

			if (completed != null && !completed.equals("A")
					&& (type == null || !type.equals("LOG"))) {
				q.setString("completed", completed);
			}

			List list = q.list();
			if (list.size() > 0 && list.get(0) != null) {
				int arg15 = Integer.parseInt(list.get(0).toString());
				return arg15;
			}
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return 0;
	}

	private String getSubSql(Date startDate, Date endDate, String type,
			String completed, long stafferId, long createrId, String fromType) {
		String str = "";
		if (startDate != null) {
			str = str + " and a.communicate_time>=:startDate";
		}

		if (endDate != null) {
			str = str + " and a.communicate_time<:endDate";
		}

		if (type != null && !type.equals("A")) {
			str = str + " and a.comm_type=:commType";
		}

		if (createrId != 0L) {
			str = str + " and a.creater_id=" + createrId;
		}

		String completeStr = "";
		if (completed != null && !completed.equals("A")) {
			completeStr = " and a.completed=:completed";
		}

		String logSql = "select a.* from  establishment_communicate a where a.comm_type=\'LOG\' and a.creater_id="
				+ stafferId + str;
		String planSql = "";
		if (fromType.equals("C")) {
			planSql = " select a.* from  establishment_communicate a where a.comm_type=\'PLAN\' and a.creater_id="
					+ stafferId + " " + str + completeStr;
		} else if (fromType.equals("J")) {
			planSql = " select a.* from  establishment_communicate a where a.comm_type=\'PLAN\' and a.creater_id!="
					+ stafferId
					+ " and a.for_all=\'Y\' "
					+ str
					+ completeStr
					+ " union all"
					+ " select a.* from"
					+ " establishment_communicate a"
					+ " ,est_comm_receiver b"
					+ " where a.id=b.comm_id"
					+ " and a.comm_type=\'"
					+ "PLAN"
					+ "\'"
					+ " and a.creater_id!="
					+ stafferId
					+ " and a.for_all=\'N\'"
					+ " and b.receiver_id="
					+ stafferId + str + completeStr;
			logSql = logSql + " and 1=0";
		} else {
			planSql = " select a.* from  establishment_communicate a where a.comm_type=\'PLAN\' and (a.creater_id="
					+ stafferId
					+ " or a.for_all=\'Y\')"
					+ str
					+ completeStr
					+ " union all"
					+ " select a.* from"
					+ " establishment_communicate a"
					+ " ,est_comm_receiver b"
					+ " where a.id=b.comm_id"
					+ " and a.comm_type=\'"
					+ "PLAN"
					+ "\'"
					+ " and a.creater_id!="
					+ stafferId
					+ " and a.for_all=\'N\'"
					+ " and b.receiver_id="
					+ stafferId + str + completeStr;
		}

		String subSql;
		if (type != null && !type.equals("A")) {
			if (type.equals("LOG")) {
				subSql = logSql;
			} else {
				subSql = planSql;
			}
		} else {
			subSql = logSql + " union all " + planSql;
		}

		return subSql;
	}

	public List getList(long parentId) {
		String sql = "from com.minierp.model.EstablishmentCommunicate c where c.parentId="
				+ parentId + " order by c.communicateTime desc";
		return this.getHibernateTemplate().find(sql);
	}

	public List getUnderList(Date startDate, Date endDate, long depId,
			String staName, String comp, long stafferId, int startIndex, int num) {
		String subSql = this.getUnderListSql(startDate, endDate, depId,
				staName, comp, stafferId);
		String sql = "select c.id,c.comm_type,c.communicate_time,c.title,c.content,c.creater_id,c.creater_name,c.completed,c.sms_remind,c.complete_mark,c.attach_file,e.est_name from ("
				+ subSql
				+ ") c left join establishment e on c.est_id=e.id"
				+ " order by c.communicate_time desc";
		Session sess = null;

		List arg15;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			if (staName != null && !staName.equals("")) {
				q.setString("staName", "%" + staName + "%");
			}

			if (comp != null && !comp.equals("A")) {
				q.setString("comp", comp);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg15 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg15;
	}

	public int getUnderListCount(Date startDate, Date endDate, long depId,
			String staName, String comp, long stafferId) {
		String subSql = this.getUnderListSql(startDate, endDate, depId,
				staName, comp, stafferId);
		String sql = "select count(c.id) from (" + subSql + ") c";
		Session sess = null;

		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			if (staName != null && !staName.equals("")) {
				q.setString("staName", "%" + staName + "%");
			}

			if (comp != null && !comp.equals("A")) {
				q.setString("comp", comp);
			}

			List list = q.list();
			if (list.size() > 0 && list.get(0) != null) {
				int arg14 = Integer.parseInt(list.get(0).toString());
				return arg14;
			}
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return 0;
	}

	private String getUnderListSql(Date startDate, Date endDate, long depId,
			String staName, String comp, long stafferId) {
		String ecStr = "";
		String staStr = "";
		if (startDate != null) {
			ecStr = ecStr + " and a.communicate_time>=:startDate";
		}

		if (endDate != null) {
			ecStr = ecStr + " and a.communicate_time<:endDate";
		}

		if (depId != 0L) {
			staStr = staStr + " and (d.id=" + depId
					+ " or (d.parent_id_str like \'%;" + depId + ";%\'))";
		}

		if (staName != null && !staName.equals("")) {
			staStr = staStr + " and (s.staffer_name like :staName)";
		}

		String completeStr = "";
		if (comp != null && !comp.equals("A")) {
			completeStr = " and a.completed=:comp";
		}

		String logSql = "select a.* from  establishment_communicate a,staffer s,department d where s.id=a.creater_id and s.department_id=d.id and a.comm_type=\'LOG\' and (s.superior_id_str like \'%;"
				+ stafferId + ";%\')" + ecStr + staStr;
		String planSql = " select a.* from establishment_communicate a where a.comm_type=\'PLAN\' and a.for_all=\'Y\'"
				+ ecStr
				+ completeStr
				+ " union all"
				+ " select a.* from "
				+ " establishment_communicate a,staffer s,department d"
				+ " where s.id=a.creater_id"
				+ " and s.department_id=d.id"
				+ " and a.for_all=\'N\'"
				+ " and a.comm_type=\'"
				+ "PLAN"
				+ "\'"
				+ " and (s.superior_id_str like \'%;"
				+ stafferId
				+ ";%\')"
				+ ecStr
				+ staStr
				+ completeStr
				+ " union all"
				+ " select a.* from"
				+ " establishment_communicate a,est_comm_receiver b,staffer s,department d"
				+ " where a.id=b.comm_id"
				+ " and s.id=b.receiver_id"
				+ " and s.department_id=d.id"
				+ " and a.for_all=\'N\'"
				+ " and s.id!=a.creater_id"
				+ " and a.comm_type=\'"
				+ "PLAN"
				+ "\'"
				+ " and (s.superior_id_str like \'%;"
				+ stafferId
				+ ";%\')" + ecStr + staStr + completeStr;
		return comp != null && comp.equals("N") ? planSql : logSql
				+ " union all " + planSql;
	}

	public int getListForEstCount(long estId) {
		String sql = "select count(*) from  com.minierp.model.EstablishmentCommunicate c where c.estId="
				+ estId;
		Session sess = null;

		int arg7;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			List list = q.list();
			if (list.isEmpty()) {
				return 0;
			}

			arg7 = Integer.parseInt(list.get(0).toString());
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg7;
	}

	public List getListForEst(long estId, int startIndex, int num) {
		String sql = "select c.id,c.creater_name,c.create_time,c.content,s.head_image from establishment_communicate c left join staffer s on c.creater_id=s.id where c.est_id="
				+ estId + " order by c.create_time desc";
		Session sess = null;

		List arg8;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg8 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg8;
	}
}